package com.san.metadata.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidPooledPreparedStatement;
import com.san.base.dao.BaseManager;
import com.san.common.datasource.JdbcManager;
import com.san.common.utils.Constant;
import com.san.common.utils.PageUtils;
import com.san.common.utils.Query;
import com.san.common.utils.StringUtils;
import com.san.metadata.domain.BaseLegalPersonDO;
import com.san.metadata.domain.BaseLegalPersonDTO;
import com.san.metadata.domain.DataSourceDO;
import com.san.metadata.vo.BaseLegalPersonVO;

/**
 * 法人基础辨识表JDBC访问层
 * 
 * @author xiangyz
 *
 */
@Service
public class BaseLegalPersonManager extends BaseManager {

	/**
	 * 根据法人名称/统一社会信用代码查询法人辨识信息
	 * 
	 * @param legalPersonName
	 * @param uscc
	 * @param isFuzzy
	 * @return
	 * @throws Exception
	 */
	public PageUtils findLegalPerson(String legalPersonName, String uscc, boolean isFuzzy, int page) throws Exception {
		DataSourceDO dataSourceDO = super.dataSourceService.getByCode(Constant.SOURCE_CODE_BASESOURCE);

		JdbcManager jdbcManager = new JdbcManager();
		List<BaseLegalPersonDO> resultList = new ArrayList<BaseLegalPersonDO>();
		Long count = 0l;
		try {
			List<Map<String, Object>> result = null;

			if (isFuzzy) {
				if (StringUtils.isEmpty(legalPersonName)) {
					return new PageUtils(resultList, 0);
				}
				if (dataSourceDO.getSourceType() == Constant.SOURCE_TYPE_ORACLE) {
					StringBuffer querySQL = new StringBuffer("select * from ( ");
					querySQL.append("SELECT ROWNUM AS rowno, t.auto_id,t.legal_person_id,t.legal_person_name,t.uscc");
					querySQL.append(" from base_legal_person where legal_person_name like '%");
					querySQL.append(legalPersonName).append("%' order by legal_person_name");
					querySQL.append("AND ROWNUM <= ").append(page * 10).append(") t");
					querySQL.append("AND t.ROWNUM > ").append((page - 1) * 10).append(" t");

					result = jdbcManager.executeQuery(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
							querySQL.toString(), null);

					StringBuffer countSQL = new StringBuffer("select count(auto_id) count ");
					countSQL.append(" from base_legal_person where legal_person_name like '%");
					countSQL.append(legalPersonName).append("%' ");
					List<Map<String, Object>> countResult = jdbcManager.executeQuery(
							this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE), countSQL.toString(), null);
					for (Map<String, Object> countMap : countResult) {
						count = (Long) countMap.get("count");
					}
				} else {
					StringBuffer querySQL = new StringBuffer("select auto_id,legal_person_id,legal_person_name,uscc");
					querySQL.append(" from base_legal_person where legal_person_name like '%");
					querySQL.append(legalPersonName).append("%' order by legal_person_name");
					querySQL.append(" limit ").append((page - 1) * 10).append(",").append(10);

					result = jdbcManager.executeQuery(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
							querySQL.toString(), null);

					StringBuffer countSQL = new StringBuffer("select count(auto_id) count ");
					countSQL.append(" from base_legal_person where legal_person_name like '%");
					countSQL.append(legalPersonName).append("%' ");
					List<Map<String, Object>> countResult = jdbcManager.executeQuery(
							this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE), countSQL.toString(), null);
					for (Map<String, Object> countMap : countResult) {
						count = (Long) countMap.get("count");
					}
				}

			} else {
				StringBuffer querySQL = new StringBuffer("select auto_id,legal_person_id,legal_person_name,uscc ");
				querySQL.append("from base_legal_person ");

				StringBuffer countSQL = new StringBuffer("select count(auto_id) count from base_legal_person ");
				int paramIndex = 0;
				List<String> params = new ArrayList<String>();
				if (!StringUtils.isEmpty(legalPersonName)) {
					querySQL.append(" where legal_person_name = ?");
					countSQL.append(" where legal_person_name = ?");
					paramIndex++;
					params.add(legalPersonName);
				}
				if (!StringUtils.isEmpty(uscc)) {
					if (0 == paramIndex) {
						querySQL.append(" where uscc = ?");
						countSQL.append(" where uscc = ?");
					} else {
						querySQL.append(" and uscc = ?");
						countSQL.append(" and uscc = ?");
					}
					paramIndex++;
					params.add(uscc);
				}

				if (0 == paramIndex) {
					// 无查询条件返回空
					return new PageUtils(resultList, 0);
				}

				result = jdbcManager.executeQuery(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
						querySQL.toString(), params.toArray());
				List<Map<String, Object>> countResult = jdbcManager.executeQuery(
						this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE), countSQL.toString(), params.toArray());
				for (Map<String, Object> countMap : countResult) {
					count = (Long) countMap.get("count");
				}
			}

			if (null != result) {
				for (Map<String, Object> resultMap : result) {
					BaseLegalPersonDO baseLegalPersonDO = new BaseLegalPersonDO();
					baseLegalPersonDO.setAutoId((Long) resultMap.get("auto_id"));
					baseLegalPersonDO.setLegalPersonId((String) resultMap.get("legal_person_id"));
					baseLegalPersonDO.setLegalPersonName((String) resultMap.get("legal_person_name"));
					baseLegalPersonDO.setUscc((String) resultMap.get("uscc"));
					resultList.add(baseLegalPersonDO);
				}
			}
		} catch (Exception e) {
			throw e;
		}

		return new PageUtils(resultList, count.intValue());
	}
	
	/**
	 * 高级查询
	 * 
	 * @param query 查询条件
	 * @return
	 * @throws Exception
	 */
	public PageUtils findLegalPersonByAdvancedQuery(Query query) throws Exception {
		JdbcManager jdbcManager = new JdbcManager();
		List<BaseLegalPersonDTO> resultList = new ArrayList<BaseLegalPersonDTO>();
		StringBuffer querySQL = new StringBuffer("select legal_person_id,f01,f24,f05,f12,");
		querySQL.append("DATE_FORMAT(STR_TO_DATE(SUBSTRING(f15,1,9),'%d-%m月-%y'),'%Y-%m-%d') clrq,f43 ");
		querySQL.append(" from qyxy where 1 = 1");
		
		List<String> params = new ArrayList<String>();
		if (!StringUtils.isEmpty((String)query.get("qymc"))) {
			//企业名称
			querySQL.append(" and f01 like '%").append((String)query.get("qymc")).append("%'");
		} else if (!StringUtils.isEmpty((String)query.get("uscc"))) {
			//统一社会信用代码
			querySQL.append(" and f24 = ? ");
			params.add((String)query.get("uscc"));
		} else if (!StringUtils.isEmpty((String)query.get("fddbrXm"))) {
			//法定代表人姓名
			querySQL.append(" and f05 = ? ");
			params.add((String)query.get("fddbrXm"));
			
		} else if (!StringUtils.isEmpty((String)query.get("phone"))) {
			//联系电话
			querySQL.append(" and f19 = ? ");
			params.add((String)query.get("phone"));
		} else if (!StringUtils.isEmpty((String)query.get("jyfw"))) {
			//经营范围
			querySQL.append(" and f14 like '%").append((String)query.get("jyfw")).append("%'");
		}
		
		if (!StringUtils.isEmpty((String)query.get("clrq"))) {
			//成立日期
			querySQL.append(" and DATE_FORMAT(STR_TO_DATE(SUBSTRING(f15,1,9),'%d-%m月-%y'),'%Y') = ?");
			params.add((String)query.get("clrq"));
		}
		
		if (!StringUtils.isEmpty((String)query.get("qyzt"))) {
			//企业状态
			if ("其他".equals((String)query.get("qyzt"))) {
				querySQL.append(" and f23 is null ");
			} else {
				querySQL.append(" and f23 = ? ");
				params.add((String)query.get("qyzt"));
			}
		}
		
		String zczb = (String)query.get("zczb");
		if (!StringUtils.isEmpty(zczb)) {
			//注册资本
			if ("1".equals(zczb)) {
				//100万以下
				querySQL.append(" and f12 < 100");
			} else if ("2".equals(zczb)) {
				//100-500万
				querySQL.append(" and f12 < 500 and f12 >= 100");
			} else if ("3".equals(zczb)) {
				//500-1000万
				querySQL.append(" and f12 < 1000 and f12 >= 500");
			} else if ("4".equals(zczb)) {
				//1000-5000万
				querySQL.append(" and f12 < 5000 and f12 >= 1000");
			} else if ("5".equals(zczb)) {
				//5000万以上
				querySQL.append(" and f12 >= 5000");
			} else if ("6".equals(zczb)) {
				//自定义
				
				if (!StringUtils.isEmpty((String)query.get("zczbMin"))) {
					querySQL.append(" and CAST(f12 AS SIGNED) >= ? ");
					params.add((String)query.get("zczbMin"));
				}
				
				if (!StringUtils.isEmpty((String)query.get("zczbMax"))) {
					querySQL.append(" and CAST(f12 AS SIGNED) < ? ");
					params.add((String)query.get("zczbMax"));
				}
			}
		}
		
		if (!StringUtils.isEmpty((String)query.get("zblx"))) {
			//资本类型
			if ("人民币".equals((String)query.get("zblx"))) {
				querySQL.append(" and f11 !='美元' and f11 != '香港元'");
			} else {
				querySQL.append(" and f11 = ? ");
				params.add((String)query.get("zblx"));
			}
		}
		
		if (!StringUtils.isEmpty((String)query.get("qylx"))) {
			//企业类型
			querySQL.append(" and f42 = ?");
			params.add((String)query.get("qylx"));
		}
		
		if (!StringUtils.isEmpty((String)query.get("xydj"))) {
			//信用等级
			querySQL.append(" and f43 = ?");
			params.add((String)query.get("xydj"));
		}
		
		if (!StringUtils.isEmpty((String)query.get("zcjg"))) {
			//注册机关
			querySQL.append(" and f44 = ? ");
			params.add((String)query.get("zcjg"));
		}
		
		if (!StringUtils.isEmpty((String)query.get("hyml"))) {
			//行业门类
			querySQL.append(" and f45 = ? ");
			params.add((String)query.get("hyml"));
		}
		
		Long total = 0l;
		String countSQL = "select count(*) as total from ("+querySQL.toString()+") t";
		List<Map<String, Object>> countResult = jdbcManager.executeQuery(
				this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE), countSQL, params.toArray());
		for (Map<String, Object> countMap : countResult) {
			total = (Long) countMap.get("total");
		}
		
		
		if (null != (Integer)query.get("offset") && null != (Integer)query.get("limit")) {
			querySQL.append(" limit ").append((Integer)query.get("offset")).append(",").append((Integer)query.get("limit"));
		} else {
			querySQL.append(" limit 0, 10 ");
		}
		
		List<Map<String, Object>> result = jdbcManager.executeQuery(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
				querySQL.toString(), params.toArray());
		
		
		if (null != result) {
			for (Map<String, Object> resultMap : result) {
				BaseLegalPersonDTO baseLegalPersonDTO = new BaseLegalPersonDTO();
				baseLegalPersonDTO.setLegalPersonId((String) resultMap.get("legal_person_id"));
				baseLegalPersonDTO.setLegalPersonName((String) resultMap.get("f01"));
				baseLegalPersonDTO.setUscc((String) resultMap.get("f24"));
				baseLegalPersonDTO.setFddbrXm((String) resultMap.get("f05"));
				baseLegalPersonDTO.setZczb((String) resultMap.get("f12"));
				baseLegalPersonDTO.setClrq((String) resultMap.get("clrq"));
				baseLegalPersonDTO.setXydj((String) resultMap.get("f43"));
				resultList.add(baseLegalPersonDTO);
			}
		}
		
		return new PageUtils(resultList, total.intValue());
	}

	/**
	 * 根据唯一标识ID/法人名称/统一社会信用代码获取法人辨识信息
	 * 
	 * @param legalPersonName
	 * @param uscc
	 * @return
	 * @throws Exception
	 */
	public List<BaseLegalPersonDO> getLegalPerson(String legalPersonName, String uscc) throws Exception {
		if (StringUtils.isEmpty(legalPersonName) && StringUtils.isEmpty(uscc)) {
			return new ArrayList<BaseLegalPersonDO>();
		}
		JdbcManager jdbcManager = new JdbcManager();
		List<Map<String, Object>> result = null;

		try {
			String querySQL = "select auto_id,legal_person_id,legal_person_name,uscc " + "from base_legal_person ";
			int paramIndex = 0;
			List<String> params = new ArrayList<String>();
			if (!StringUtils.isEmpty(legalPersonName)) {
				querySQL += " where legal_person_name = ?";
				paramIndex++;
				params.add(legalPersonName);
			}
			if (!StringUtils.isEmpty(uscc)) {
				if (0 == paramIndex) {
					querySQL += " where uscc = ?";
				} else {
					querySQL += " and uscc = ?";
				}
				params.add(uscc);
			}

			result = jdbcManager.executeQuery(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE), querySQL,
					params.toArray());
		} catch (Exception e) {
			throw e;
		}

		List<BaseLegalPersonDO> resultList = new ArrayList<BaseLegalPersonDO>();
		if (null != result) {
			for (Map<String, Object> resultMap : result) {
				BaseLegalPersonDO baseLegalPersonDO = new BaseLegalPersonDO();
				baseLegalPersonDO.setAutoId((Long) resultMap.get("auto_id"));
				baseLegalPersonDO.setLegalPersonId((String) resultMap.get("legal_person_id"));
				baseLegalPersonDO.setLegalPersonName((String) resultMap.get("legal_person_name"));
				baseLegalPersonDO.setUscc((String) resultMap.get("uscc"));
				resultList.add(baseLegalPersonDO);
			}
		}

		return resultList;
	}

	/**
	 * 批量保存法人基础辨识信息
	 * 
	 * @param addBaseList
	 * @return
	 */
	public int batchSave(List<BaseLegalPersonVO> list) throws Exception {

		try {
			String insertSQL = "insert into base_legal_person(legal_person_id,legal_person_name,uscc) value(?,?,?)";

			Connection conn = this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE);
			DruidPooledPreparedStatement ps = null;
			try {
				ps = (DruidPooledPreparedStatement) conn.prepareStatement(insertSQL);
				// 设置为手动提交
				conn.setAutoCommit(false);
				for (BaseLegalPersonVO baseLegalPersonVO : list) {
					ps.setObject(1, baseLegalPersonVO.getLegalPersonId());
					ps.setObject(2, baseLegalPersonVO.getLegalPersonName());
					ps.setObject(3, baseLegalPersonVO.getUscc());
					ps.addBatch();
				}

				ps.executeBatch();
				// 提交，批量插入数据库中。
				conn.commit();
			} catch (SQLException e) {
				throw e;
			} finally {
				if (null != ps) {
					ps.close();
				}
				if (null != conn) {
					conn.close();
				}
			}
		} catch (Exception e) {
			throw e;
		}

		return list.size();
	}

	/**
	 * 批量保存法人基础辨识信息
	 * 
	 * @param updateBaseList
	 * @return
	 */
	public int batchUpdate(List<BaseLegalPersonVO> list) throws Exception {
		JdbcManager jdbcManager = new JdbcManager();

		try {
			String insertSQL = "update base_legal_person set uscc = ? where legal_person_name = ?";
			List<Object[]> updateList = new ArrayList<Object[]>();
			for (BaseLegalPersonVO baseLegalPersonVO : list) {
				updateList.add(new Object[] { baseLegalPersonVO.getUscc(),baseLegalPersonVO.getLegalPersonName() });
			}
			jdbcManager.batchExecuteSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE), insertSQL, updateList);
		} catch (Exception e) {
			throw e;
		}

		return list.size();
	}
}
